﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Model;
using Qianli.DirSir.Common;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace Qianli.DirSir.DAL
{
    public class GoodsDAL
    {
       //SQLHelper helper = new SQLHelper();
       ///// <summary>
       ///// 显示商品信息
       ///// </summary>
       ///// <returns></returns>
       //public List<GoodsInfo> Goodshow()
       //{
       //    string sql = "select GoodsInfo.GoodsID,GoodsInfo.GoodsCoding,GoodsInfo.GoodsName,GoodsInfo.Barcode,GoodsInfo.Remarks,GoodsInfo.[Weight],GoodsTypeInfo.TypeID,GoodsTypeInfo.TypeName from GoodsInfo inner join GoodsTypeInfo on GoodsInfo.TypeID=GoodsTypeInfo.TypeID";
       //    DataTable table = helper.ExecDataTable(sql);
       //    List<GoodsInfo> list = new List<GoodsInfo>();
       //    foreach (DataRow item in table.Rows)
       //    {
       //        GoodsInfo g = new GoodsInfo();
       //        g.GoodsID = Convert.ToInt32(item["GoodsID"]);
       //        g.GoodsCoding = item["GoodsCoding"].ToString();
       //        g.GoodsName = item["GoodsName"].ToString();
       //        g.Barcode = item["Barcode"].ToString();
       //        g.Remarks = item["Remarks"].ToString();
       //        if (item["Weight"]==null)
       //        {
       //            g.Weight = 0;
       //        }
       //        else
       //        {
       //            g.Weight = Convert.ToInt32(item["Weight"]);
       //        } 
       //        g.TypeID = Convert.ToInt32(item["TypeID"]);
       //        g.TypeName = item["TypeName"].ToString();
       //        list.Add(g);
       //    }
       //    return list;
       //}
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 显示商品信息
        /// </summary>
        /// <returns></returns>
        public List<GoodsInfo> Goodshow()
        {
            string sql = "select GoodsID,TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,Num from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID";
            DataTable table = helper.ExecDataTable(sql);
            List<GoodsInfo> list = new List<GoodsInfo>();
            foreach (DataRow item in table.Rows)
            {
                GoodsInfo g = new GoodsInfo();
                g.GoodsID = Convert.ToInt32(item["GoodsID"]);
                g.TypeName = item["TypeName"].ToString();
                g.GoodsCoding = item["GoodsCoding"].ToString();
                g.GoodsName = item["GoodsName"].ToString();
                g.Remarks = item["Remarks"].ToString();
                g.StandardCost = Convert.ToInt32(item["StandardCost"]);
                g.WholesalePrice = Convert.ToInt32(item["WholesalePrice"]);
                g.Num = Convert.ToInt32(item["Num"]);
                list.Add(g);
            }
            return list;
        }

        /// <summary>
        /// 显示商品分类
        /// </summary>
        /// <returns></returns>
        public List<GoodsTypeInfo> showGoodsType()
        {
            string sql = "select * from GoodsTypeInfo";
            DataTable table = helper.ExecDataTable(sql);
            List<GoodsTypeInfo> list = new List<GoodsTypeInfo>();
            foreach (DataRow item in table.Rows)
            {
                GoodsTypeInfo t = new GoodsTypeInfo();
                t.TypeID = Convert.ToInt32(item["TypeID"]);
                t.TypeName = item["TypeName"].ToString();
                list.Add(t);
            }
            return list;
        }

       ///// <summary>
       ///// 添加
       ///// </summary>
       ///// <param name="ss"></param>
       ///// <returns></returns>
       //public int addGoods(GoodsInfo ss)
       //{
       //    if(ss.GoodsCoding=="")
       //    {
       //        ss.GoodsCoding = System.Guid.NewGuid().ToString();
       //        string sql = "insert into GoodsInfo(GoodsCoding,GoodsName,Remarks,Barcode,[Weight],TypeID) values('" + ss.GoodsCoding + "','" + ss.GoodsName + "','" + ss.Remarks + "','" + ss.Barcode + "'," + ss.Weight + "," + ss.TypeID + ")";
       //        return helper.ExecNonquery(sql);
       //    }
       //    else
       //    {
       //        string sql = "insert into GoodsInfo(GoodsCoding,GoodsName,Remarks,Barcode,[Weight],TypeID) values('" + ss.GoodsCoding + "','" + ss.GoodsName + "','" + ss.Remarks + "','" + ss.Barcode + "'," + ss.Weight + "," + ss.TypeID + ")";
       //        return helper.ExecNonquery(sql);
       //    }
           
       //}
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="ss"></param>
        /// <returns></returns>
        public int addGoods(GoodsInfo ss)
        {
            if (ss.GoodsCoding ==null)
            {
                ss.GoodsCoding = System.Guid.NewGuid().ToString();
            }

            StringBuilder builder = new StringBuilder();
            builder.Append("insert into GoodsInfo(TypeID,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,Num) values(");
            builder.Append("@TypeID,@GoodsCoding,@GoodsName,@Remarks,@StandardCost,@WholesalePrice,@Num)");

            SqlParameter[] parmeter ={ 
                new SqlParameter("@TypeID",SqlDbType.Int),
                new SqlParameter("@GoodsCoding",SqlDbType.VarChar),
                new SqlParameter("@GoodsName",SqlDbType.VarChar,30),
                new SqlParameter("@Remarks",SqlDbType.VarChar,30),
                new SqlParameter("@StandardCost",SqlDbType.VarChar,30),
                new SqlParameter("@WholesalePrice",SqlDbType.VarChar,30),
                new SqlParameter("@Num",SqlDbType.VarChar,30),
           };
            parmeter[0].Value = ss.TypeID;
            parmeter[1].Value = ss.GoodsCoding;
            parmeter[2].Value = ss.GoodsName;
            parmeter[3].Value = ss.Remarks;
            parmeter[4].Value = ss.StandardCost;
            parmeter[5].Value = ss.WholesalePrice;
            parmeter[6].Value = ss.Num;
            int i = helper.ExecNonquery(builder.ToString(), parmeter);
            return i;


        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int deleteGoods(int id)
        {
            string sql = "delete from GoodsInfo where GoodsID=" + id + "";
            return helper.ExecNonquery(sql);
        }

        /// <summary>
        /// 根据id获取goods表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public GoodsInfo GetGoodsByID(int id)
        {
            string sql = "select GoodsID,GoodsTypeInfo.TypeID,GoodsTypeInfo.TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,Num from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID where GoodsID=@id ";
            GoodsInfo g = new GoodsInfo();

            SqlParameter[] parameter = { 
                                      new SqlParameter("@id",SqlDbType.Int,4) 
                                       };
            parameter[0].Value = id;

            DataTable d = helper.ExecDataTable(sql, parameter);
            g.GoodsID = Convert.ToInt32(d.Rows[0]["GoodsID"]);          
            g.TypeID = Convert.ToInt32(d.Rows[0]["TypeID"]);
            g.TypeName = d.Rows[0]["TypeName"].ToString();
            g.GoodsCoding = d.Rows[0]["GoodsCoding"].ToString();
            g.GoodsName = d.Rows[0]["GoodsName"].ToString();
            g.Remarks = d.Rows[0]["Remarks"].ToString();
            g.StandardCost = Convert.ToInt32(d.Rows[0]["StandardCost"]);
            g.WholesalePrice = Convert.ToInt32(d.Rows[0]["WholesalePrice"]);
            g.Num = Convert.ToInt32(d.Rows[0]["Num"]);

            return g;
        }
        /// <summary>
        /// 修改物品信息
        /// </summary>
        /// <param name="g"></param>
        /// <returns></returns>
        public int UptGoods(GoodsInfo g)
        {
            string sql = "update GoodsInfo set TypeID= '" + g.TypeID + "',GoodsCoding=' " + g.GoodsCoding + "',GoodsName=' " + g.GoodsName + "',Remarks= '" + g.Remarks + "',StandardCost=' " + g.StandardCost + "',WholesalePrice=' " + g.WholesalePrice + "',Num=' " + g.Num + "' where GoodsID=' " + g.GoodsID + "'";
            return helper.ExecNonquery(sql);

        }

        /// <summary>
        /// 根据姓名搜索物品表信息
        /// </summary>
        /// <param name="GoodsName"></param>
        /// <returns></returns>
        public List<GoodsInfo> SearchGoods(string GoodsName)
        {
            string sql = "select GoodsID,TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,Num from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID where GoodsName like @GoodsName";

            SqlParameter[] parameter = { 
                 new SqlParameter("@GoodsName",SqlDbType.VarChar,30)                      
                                       };
            parameter[0].Value = "%" + GoodsName + "%";

            DataTable table = helper.ExecDataTable(sql,parameter);
            List<GoodsInfo> list = new List<GoodsInfo>();
            foreach (DataRow item in table.Rows)
            {
                GoodsInfo g = new GoodsInfo();
                g.GoodsID = Convert.ToInt32(item["GoodsID"]);
                g.TypeName = item["TypeName"].ToString();
                g.GoodsCoding = item["GoodsCoding"].ToString();
                g.GoodsName = item["GoodsName"].ToString();
                g.Remarks = item["Remarks"].ToString();
                g.StandardCost = Convert.ToInt32(item["StandardCost"]);
                g.WholesalePrice = Convert.ToInt32(item["WholesalePrice"]);
                g.Num = Convert.ToInt32(item["Num"]);
                list.Add(g);
            }
            return list;



        }

    }
}
